1.1 SQL 파싱과 최적화
1.1.1 구조적, 집합적, 선언적 질의 언어
SQL은 기본적으로 구조적이고 집합적이며 선언적인 질의 언어입니다. 결과집합을 만드는 과정은 절차적일 수밖에 없기 때문에 옵티마이저가 대신 절차적으로 작성을 해줍니다.
DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행 가능한 상태를 만드는 과정을 ‘SQL 최적화’라고 합니다.
1.1.2 SQL 최적화
SQL 실행 전 세분화 과정
-
SQL 파싱
파싱 트리 생성 - Syntax체크 - Semantic 체크
파싱 트리 생성, 문법적 오류 확인, 의미상 오류 확인
-
SQL 최적화
옵티마이저는 미리 수집한 오브젝트 통계정보를 바탕으로 실행경로를 생성 후 효율적인 하나를 선택
-
로우 소스 생성
옵티마이저가 선택한 실행경로를 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅
1.1.3 SQL 옵티마이저
사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 최적의 데이터 엑세스 경로를 선택해 주는 DBMS 엔진입니다.
옵티마이저의 최적화 단계
- 사용자로부터 전달받은 쿼리를 수행하는데 후보군이 될만한 실행계획 확인
- 데이터 딕셔너리에 수집해둔 오브젝트 통계 및 시스템 통계정보를 이용해 실행계획 예상비용 산정
- 최저 비용을 나타내는 실행계획 선택
1.1.4 실행계획과 비용
DBMS의 SQL 실행경로 미리보기(실행계획)은 옵티마이저가 생성한 처리절차를 사용자가 확인할 수 있게 트리구조로 표현한 것입니다.
옵티마이저가 T_X01 인덱스를 선택한 이유는 Cost를 근거하여 선택했습니다.
Cost(비용)은 쿼리를 수행하는 동안 발생할 것으로 예상하는 I/O 횟수 또는 예상 소요시간입니다.
통계정보를 활용해 계산해 낸 값이므로 실측치와 다를 수 있습니다.
1.1.5 옵티마이저 힌트
옵티마이저는 항상 최선의 선택이 아니기 때문에 개발자가 직접 효율적인 경로를 찾아낼 때 힌트를 이용해 엑세스 경로를 바꾸기 위해 사용합니다.
힌트 사용법은 /*+ … */
SELECT /*+ INDEX(A 고객_PK) */
고객명, 연락처, 주소, 가입일시
FROM 고객 A
WHERE 고객ID = '000000'
자주 사용하는 힌트 목록
| 최적화 목표 | ALL_ROWS | 전체 처리속도 최적화 |
| FIRST_ROWS (N) | 최초 N건 응답속도 최적화 | |
| FULL | Table Full Scan으로 유도 | |
| INDEX | Index Scan으로 유도 | |
| 액세스 방식 | INDEX_DESC | Index를 역순으로 스캔하도록 유도 |
| INDEX_FFS | Index Fast Full Scan으로 유도 | |
| INDEX_SS | Index Skip Scan으로 유도 | |
| 조인 순서 | ORDERED | FROM 절에 나열된 순서대로 조인 |
| LEADING | LEADING 힌트 괄호에 기술한 순서대로 조인 (예) LEADING(T1 T2) | |
| SWAP_JOIN_INPUTS | 해시 조인 시, BUILD INPUT을 명시적으로 선택 (예) SWAP_JOIN_INPUTS (T1) | |
| USE_NL | NL 조인으로 유도 | |
| USE_MERGE | 소트 머지 조인으로 유도 | |
| USE_HASH | 해시 조인으로 유도 | |
| 조인 방식 | NL_SJ | NL 세미조인으로 유도 |
| MERGE_SJ | 소트 머지 세미조인으로 유도 | |
| 서브쿼리 | HASH_SJ | 해시 세미조인으로 유도 |
| MATERIALIZE | WITH 문으로 정의한 집합을 물리적으로 생성하도록 유도 | |
| 팩토링 | INLINE | WITH 문으로 정의한 집합을 물리적으로 생성하지 않고 INLINE 처리하도록 유도 |
| MERGE | 뷰 머징 유도 | |
| NO_MERGE | 뷰 머징 방지 | |
| UNNEST | 서브쿼리 Unnesting 유도 | |
| NO_UNNEST | 서브쿼리 Unnesting 방지 | |
| 쿼리 변환 | PUSH_PRED | 조인조건 Pushdown 유도 |
| NO_PUSH_PRED | 조인조건 Pushdown 방지 | |
| USE_CONCAT | OR 또는 IN-List 조건을 OR-Expansion으로 유도 | |
| NO_EXPAND | OR 또는 IN-List 조건에 대한 OR-Expansion 방지 | |
| 병렬 처리 | PARALLEL | 테이블 스캔 또는 DML을 병렬방식으로 처리하도록 유도 |
| PARALLEL_INDEX | 인덱스 스캔을 병렬방식으로 처리하도록 유도 | |
| PQ_DISTRIBUTE | 병렬 수행 시 데이터 분배 방식 결정 (예) PQ_DISTRIBUTE (T1 HASH HASH) | |
| APPEND | Direct-Path Insert로 유도 | |
| 기타 | DRIVING_SITE | DB Link Remote 쿼리에 대한 최적화 및 실행 주체 지정 (Local 또는 Remote) |
| PUSH_SUBQ | 서브쿼리를 가급적 빨리 필터링하도록 유도 | |
| NO_PUSH_SUBQ | 서브쿼리를 가급적 늦게 필터링하도록 유도 |
1.2 SQL 공유 및 재사용
소프트 파싱과 하드파싱의 차이점 설명 및 동시성이 높은 온라인 트랜잭션 처리 시스템에서 변수의 중요성
1.2.1 소프트 파싱 vs 하드 파싱
라이브러리 캐시는 SQL 파싱, 최적화, 로우 소스 생성 과정을 거쳐 생성한 내부 프로시저를 재사용할 수 있도록 캐싱해 두는 메모리 공간입니다.
SGA는 서버 프로세스와 백그라운드 프로세스가 공통으로 엑세스하는 데이터와 제어 구조를 캐싱하는 메모리 공간입니다. 라이브러리 캐시는 SGA의 구성요소입니다.
💡 사용자가 SQL문을 전달하면 DBMS는 SQL을 파싱 후 라이브러리 캐시 여부를 확인합니다. 캐시에서 찾으면 곧바로 실행 단계로 넘어가지만, 찾지 못하면 최적화 단계를 거칩니다.
SQL 최적화 과정이 어려운 이유
쿼리문 하나를 최적화하는 데도 많은 경우의 수가 존재합니다.
- 테이블, 컬럼, 인덱스 구조에 관한 기본정보
- 오브젝트 통계: 테이블 통계, 인덱스 통계, 컬럼 통계
- 시스템 통계: CPU 속도, Single Block I/O 속도, Multiblock I/O 속도 등
- 옵티마이저 관련 파라미터
하나의 쿼리를 수행하는 데 많은 실행경로를 도출하고, 짧은 순간에 딕셔너리와 통계정보를 읽어 효율성을 판단하는 과정이 어려운 작업이므로, 한 번만 사용하면 효율적이지 않아 라이브러리 캐시가 필요합니다.
1.2.2 바인드 변수의 중요성
사용자 정의 함수/프로시저는 내용이 변해도 이름은 변하지 않으므로 여러 사용자가 공유하면서 재사용할 수 있습니다.
SQL은 이름이 따로 없고 SQL 자체가 이름이기 때문에 텍스트가 변경되면 이름도 변경됩니다. 특히 개발 과정에서 수시로 변경이 일어나기에 DBMS가 영구 저장하지 않습니다.
공유기능 SQL
캐시에서 키 값이 ‘SQL 문 그 자체’이므로 대소문자, 힌트, 띄어쓰기를 모두 구분하여 의미적으로는 같지만 별도의 캐시 공간을 사용합니다.
DBMS에 발생하는 부하는 대부분 I/O가 원인이지만, 캐싱이 되지 않아 I/O는 발생하지 않지만 CPU가 급격히 올라가 각 프로그램 실행 SQL 문을 하나씩 라이브러리에 적재하는 경우가 됩니다.
파라미터 Driven 방식으로 SQL을 작성하여 캐싱되도록 수정하고 캐시(V$SQL)를 조회하면 하드파싱이 한 번만 일어나고 캐싱된 SQL을 여러 사용자가 사용할 수 있습니다.
1.3 데이터 저장 구조 및 I/O 메커니즘
SQL 튜닝을 시작하기 앞서 데이터 저장 구조, 디스크 및 메모리에서 데이터를 읽는 메커니즘을 확인합니다.
1.3.1 SQL이 느린 이유
SQL이 느린 이유는 디스크 I/O 때문입니다.
디스크 I/O (Input/Output)가 많으면 Process는 waiting 상태에서 I/O가 완료되길 기다립니다. waiting 상태가 길어질수록 SQL문은 느려집니다.
1.3.2 데이터베이스 저장 구조
테이블스페이스 > 세그먼트(테이블) > 익스텐트 > 블록 > 로우
테이블스페이스: 세그먼트를 담는 컨테이너로 여러 개의 데이터파일로 구성
세그먼트: 테이블, 인덱스처럼 데이터 저장공간이 필요한 오브젝트. 파티션 구조가 아니라면 테이블과 인덱스도 하나의 세그먼트입니다.
익스텐트: 공간을 확장하는 단위. 테이블이나 인덱스에 공간이 부족해지면 해당 오브젝트가 속한 테이블스페이스로부터 익스텐트를 추가로 할당 받습니다. 연속된 여러 개의 데이터 블록으로 구성됩니다.
블록: 사용자가 입력한 레코드를 실제 저장하는 공간입니다.
요약
데이터파일: 디스크 상의 물리적인 OS 파일
테이블스페이스: 세그먼트를 담는 컨테이너
세그먼트: 데이터 저장공간이 필요한 오브젝트(테이블, 인덱스, 파티션, LOB 등)
익스텐트: 공간을 확장하는 단위, 연속된 블록 집합
블록: 데이터를 읽고 쓰는 단위
익스텐트 내 블록은 인접한 연속된 공간이지만, 서로 같은 연속된 공간은 아닙니다.
💡 오라클에서의 세그먼트에 할당된 익스텐트 목록 조회
SELECT SEGMENT_TYPE, TABLESPACE_NAME, EXTENT_ID, FILE_ID, BLOCK_ID, BLOCKS
FROM DBA_SEGMENTS
WHERE OWNER = USER
AND SEGMENT_NAME = 'my_seg'
ORDER BY EXTENT_ID;
1.3.3 블록 단위 I/O
DBMS가 데이터를 읽고 쓰는 단위는 블록입니다. 특정 레코드 하나를 읽고 싶어도 해당 블록을 통째로 읽습니다.
오라클은 기본적으로 8KB 크기의 블록을 사용하므로 1Byte를 읽기 위해 8KB를 읽습니다.
1.3.4 시퀀셜 엑세스 / 랜덤 엑세스
테이블 또는 인덱스 블록 엑세스하는 방식은 시퀀셜, 랜덤 엑세스가 있습니다.
- 시퀀셜 엑세스: 물리적, 논리적 연결된 순서에 따라 순차적으로 접근
- 랜덤 엑세스: 순서에 따르지 않고 한 블록씩 접근
1.3.5 논리적 I/O | 물리적 I/O
####DB 버퍼캐시
디스크 I/O가 성능을 결정하기에 자주 읽는 블록을 매번 디스크에서 읽는 것은 비효율적입니다. 디스크에서 어렵게 읽은 데이터를 데이터 블록에 캐싱해 반복적인 I/O Call을 줄이는 데 목적이 있습니다.
–버퍼캐시 사이즈 확인 –V$SGA 뷰를 통해 확인
show sga
| 논리적 I/O | 물리적 I/O |
- 논리적 블록 I/O: SQL을 처리하는 과정에서 발생한 총 블록 I/O
- 물리적 블록 I/O: 디스크에서 발생한 총 블록 I/O, SQL 처리 중 블록 버퍼캐시를 찾지 못할 때만 디스크 엑세스
1.3.6 Single Block I/O | MultiBlock I/O
- Single Block I/O: 한 번에 한 블록씩 요청해서 메모리에 적재하는 방식
- MultiBlock I/O: 한 번에 여러 블록씩 요청해서 메모리에 적재하는 방식
1.3.7 Table Full Scan / Index Range Scan
데이터를 읽는 방식은
- 테이블 전체를 스캔해서 읽는 방식
- 인덱스를 이용해서 읽는 방식
Table Full Scan은 테이블에 속한 블록 전체를 읽어서 데이터를 찾는 방식입니다. Index에서 일정량을 스캔해서 얻은 ROWID로 테이블 레코드를 찾는 방식입니다.
Table Full Scan은 피해야 한다는 인식과 달리 인덱스가 SQL 성능을 떨어뜨리는 경우도 많습니다.
배치 프로그램 같은 경우 Table Full Scan을 유도하고 조인 메소드로 해시 조인을 선택하면 성능이 좋아지는 경우가 많습니다. 많은 데이터를 읽을 때는 Table Full Scan이 효과적이고, 인덱스는 큰 테이블에서 작은 데이터를 찾기 위한 도구입니다.
